<?php
/**
 * Created by PhpStorm.
 * User: wodrow
 * Date: 17-11-15
 * Time: 下午5:07
 */

namespace workin\modules\others\controllers;


use common\components\tools\Excel;
use common\components\tools\Tools;
use common\models\CarOrderBankBackLog;
use common\models\credit_app\Member;
use workin\modules\others\models\ExcelUploadForm;
use yii\base\ErrorException;
use yii\helpers\FileHelper;
use yii\web\Controller;
use yii\web\UploadedFile;

class DefaultController extends Controller
{
    public function actionTest()
    {
        echo 132456;
    }

    public function actionImportSiteExcel()
    {
        $model = new ExcelUploadForm();
        if(\Yii::$app->request->isPost){
            $tmp_path = \Yii::$app->getRuntimePath().DIRECTORY_SEPARATOR."uploads";
            if (!is_dir($tmp_path)){
                FileHelper::createDirectory($tmp_path);
            }
            $model->file = UploadedFile::getInstance($model, 'file');
            if ($model->validate()){
                $saveAs = ($tmp_path . '/' . date("Y-m-d") . '.' . time() . '.' . $model->file->extension);
                $is_save = $model->file->saveAs($saveAs);
                if($is_save){
                    $x = Excel::formatExcelToArray($saveAs);
                    unlink($saveAs);
                    \Yii::trace($x, 'wodrow');
                }
            }
        }
        return $this->render('import-site-excel', [
            'model' => $model,
        ]);
    }

    public function actionImportMemberExcel()
    {
        $model = new ExcelUploadForm();
        if(\Yii::$app->request->isPost){
            $tmp_path = \Yii::$app->getRuntimePath().DIRECTORY_SEPARATOR."uploads";
            if (!is_dir($tmp_path)){
                FileHelper::createDirectory($tmp_path);
            }
            $model->file = UploadedFile::getInstance($model, 'file');
            if ($model->validate()){
                $saveAs = ($tmp_path . '/' . date("Y-m-d") . '.' . time() . '.' . $model->file->extension);
                $is_save = $model->file->saveAs($saveAs);
                if($is_save){
                    $x = Excel::formatExcelToArray($saveAs);
                    unlink($saveAs);
                    if (count($x)<2){
                        throw new ErrorException("no input data");
                    }
                    $keys = $x[1];
                    $y = [];
                    $data = [];
                    foreach ($x as $k => $v){
                        foreach ($v as $k1 => $v1){
                            if ($keys[$k1]!=$v1){
                                $y[$keys[$k1]] = $v1;
                            }
                        }
                        if ($y){
                            $data[] = $y;
                        }
                    }
                    Member::importMembers($data);
                }
            }
        }
        return $this->render('import-member-excel', [
            'model' => $model,
        ]);
    }

    public function actionCheckMemberImportLog($file){
        return $this->render('check-member-import-log', [
            'file' => $file,
        ]);
    }

    public function actionGetCarOrderBackFilterByOrderno($orderno='')
    {
        $data = [];
        if ($orderno){
            $sql = "SELECT 
	FROM_UNIXTIME(car_order_log.created_at) AS 抛送时间,
  api_return_msg AS 抛送结果,
 FROM_UNIXTIME(car_order_bank_back_log.created_at) AS 退回时间,
 car_order_bank_back_log.request_data,
 car_order.cust_name,car_order.custom_id, custom.orderno FROM car_order_log 
LEFT JOIN car_order_bank_back_log ON for_serial_number=serial_number
LEFT JOIN car_order ON car_order_log.car_order_id = car_order.id
LEFT JOIN custom ON car_order.custom_id = custom.id
WHERE custom.orderno = '{$orderno}';";
            $data = \Yii::$app->db->createCommand($sql)->query()->readAll();
        }
        return $this->render('get-car-order-back-filter-by-orderno', [
            'data' => $data,
        ]);
    }

    /**
     * 今天未处理的car_order退回
     * @param string $orderno
     * @return string
     * @throws \yii\db\Exception
     */
    public function actionShow1($guarantee_company_id = 1)
    {
        /*$date = date("Y-m-d");
        $ts = $date." 00:00:00";
        $ts = strtotime($ts);
        $data = CarOrderBankBackLog::find()->where(['>', 'created_at', $ts])->all();
        var_dump($data);exit;
        $data = [];*/
        if ($guarantee_company_id){
            $sql = "SELECT FROM_UNIXTIME(car_order_bank_back_log.created_at) AS 退回时间, 
request_data AS 退回原因,
cust_name, 
custom_id,
orderno
FROM car_order_bank_back_log 
LEFT JOIN car_order_log ON for_serial_number = serial_number
LEFT JOIN car_order ON car_order_id = car_order.id
LEFT JOIN custom ON car_order.custom_id = custom.id
WHERE car_order_bank_back_log.created_at>UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE)) 
AND guarantee_company_id = {$guarantee_company_id};
;";
            $data = \Yii::$app->db->createCommand($sql)->query()->readAll();
        }
        return $this->render('show1', [
            'data' => $data,
        ]);
    }

    /**
     * 最近30天的special_card退回记录
     * @param string $orderno
     * @return string
     * @throws \yii\db\Exception
     */
    public function actionShow3($guarantee_company_id = 1)
    {
        /*$date = date("Y-m-d");
        $ts = $date." 00:00:00";
        $ts = strtotime($ts);
        $data = CarOrderBankBackLog::find()->where(['>', 'created_at', $ts])->all();
        var_dump($data);exit;
        $data = [];*/
        if ($guarantee_company_id){
            $sql = "SELECT FROM_UNIXTIME(special_card_bank_back_log.created_at) AS '退回时间', 
request_data AS '退回原因', 
master_name, 
custom_id, 
orderno 
FROM special_card_bank_back_log
LEFT JOIN special_card_ask_log ON for_serial_number = serial_number
LEFT JOIN special_card ON special_card_id = special_card.id
LEFT JOIN custom ON custom_id = custom.id
WHERE special_card_bank_back_log.created_at>UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE)) - 3600*24*30
AND guarantee_company_id = {$guarantee_company_id};
;";
            $data = \Yii::$app->db->createCommand($sql)->query()->readAll();
        }
        return $this->render('show3', [
            'data' => $data,
        ]);
    }

    public function actionGetSpecialCardBackFilterByOrderno($orderno='')
    {
        $data = [];
        if ($orderno){
            $sql = "SELECT 
	FROM_UNIXTIME(special_card_ask_log.time) AS 抛送时间,
  api_return_msg AS 抛送结果,
 FROM_UNIXTIME(special_card_bank_back_log.created_at) AS 退回时间,
 special_card_bank_back_log.request_data,
 custom.master_name,special_card.custom_id, custom.orderno FROM special_card_ask_log 
LEFT JOIN special_card_bank_back_log ON for_serial_number=serial_number
LEFT JOIN special_card ON special_card_ask_log.special_card_id = special_card.id
LEFT JOIN custom ON special_card.custom_id = custom.id
WHERE custom.orderno = '{$orderno}';";
            $data = \Yii::$app->db->createCommand($sql)->query()->readAll();
        }
        return $this->render('get-special-card-back-filter-by-orderno', [
            'data' => $data,
        ]);
    }

    public function actionShow2($bank_id = 1, $date_start = null, $date_end = null)
    {
        $sql = "SELECT 
credit_order_log.time as col_time,
FROM_UNIXTIME(credit_order_log.time) AS 发送到ICBC时间, 
orderno AS ICBC订单号, 
custname AS 被征信人, 
idno AS 身份证号, 
relation AS 订单中角色,  
api_return_msg AS 即时机器反馈, 
`status` AS 13人工已反馈，11等待人工反馈, 
FROM_UNIXTIME(bank_back_send_log.time) AS 人工反馈时间, 
request_data AS 退回原因,
request_data AS 是否正常001、099,
request_data AS 点掉内容如果不是001
FROM `credit_order_log` 
LEFT JOIN credit_order ON credit_order_id = credit_order.id
LEFT JOIN bank_back_send_log ON bank_back_send_log.for_serial_number = credit_order_log.serial_number
LEFT JOIN custom ON custom_id = custom.id
where bank_id={$bank_id}";
        if (!$date_start){
            $date_start = date("Y-m-d");
        }
        if (!$date_end){
            $date_end = date("Y-m-d");
        }
        $st = strtotime($date_start);
        $et = strtotime($date_end)+86400;
        $sql .= " AND credit_order_log.time > {$st} AND credit_order_log.time < {$et}";
        $data = \Yii::$app->db->createCommand($sql)->query()->readAll();
        return $this->render('show2', [
            'data' => $data,
        ]);
    }

    /**
     * 生成车架号
     * @return string
     */
    public function actionVinGenerate()
    {
        return $this->renderPartial('vin-generate');
    }
}